﻿#define INCLUDE_WEB_FUNCTIONS

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Data;
using System.Reflection;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using RestApp.BusinessLogic.BusinessModels;

namespace RestApp.UI.Core
{
    public class ExcelCreator
    {

        //  November 2013
        //  http://www.mikesknowledgebase.com
        //
        //  Note: if you plan to use this in an ASP.Net application, remember to add a reference to "System.Web", and to uncomment
        //  the "INCLUDE_WEB_FUNCTIONS" definition at the top of this file.
        //
        //  Release history
        //   - Nov 2013: 
        //        Changed "CreateExcelDocument(DataTable dt, string xlsxFilePath)" to remove the DataTable from the DataSet after creating the Excel file.
        //        You can now create an Excel file via a Stream (making it more ASP.Net friendly)
        //   - Jan 2013: Fix: Couldn't open .xlsx files using OLEDB  (was missing "WorkbookStylesPart" part)
        //   - Nov 2012: 
        //        List<>s with Nullable columns weren't be handled properly.
        //        If a value in a numeric column doesn't have any data, don't write anything to the Excel file (previously, it'd write a '0')
        //   - Jul 2012: Fix: Some worksheets weren't exporting their numeric data properly, causing "Excel found unreadable content in '___.xslx'" errors.
        //   - Mar 2012: Fixed issue, where Microsoft.ACE.OLEDB.12.0 wasn't able to connect to the Excel files created using this class.
        //
            public static bool ExportReportToExcel(WorkTimeReport report, string xlsxFilePath, System.Web.HttpResponseBase Response)
            {

                DataTable dt = new DataTable();

                foreach (var item in report.WorkTimeItems)
                {
                    dt.Columns.Add(new DataColumn("Дата", GetNullableType(item.DateWork.GetType())));
                    dt.Columns.Add(new DataColumn("Начало работы по графику", GetNullableType(item.GraphicStartWork.GetType())));
                    dt.Columns.Add(new DataColumn("Окончание работы по графику", GetNullableType(item.GraphicEndWork.GetType())));
                    dt.Columns.Add(new DataColumn("Фактическое время начала работы", GetNullableType(item.FactStartWork.GetType())));
                    dt.Columns.Add(new DataColumn("Фактическое время окончания работы", GetNullableType(item.FactEndWork.GetType())));
                    dt.Columns.Add(new DataColumn("Уважительная причина отсутствия", GetNullableType(typeof(string))));
                    dt.Columns.Add(new DataColumn("Прогул", GetNullableType(typeof(string))));
                    dt.Columns.Add(new DataColumn("Комментарий", GetNullableType(typeof(string)))); 
                    dt.Columns.Add(new DataColumn("Оплата без штрафов", GetNullableType(item.MustBePay.GetType()))); 
                    dt.Columns.Add(new DataColumn("Фактическая оплата", GetNullableType(item.FactPay.GetType())));
                    dt.Columns.Add(new DataColumn("Штрафы", GetNullableType(item.Penalties.GetType())));
                    dt.Columns.Add(new DataColumn("Комментарий к оплате", GetNullableType(typeof(string))));

					break;
                }

                foreach (var item in report.WorkTimeItems)
                {
                    DataRow row = dt.NewRow();
                    row["Дата"] = item.DateWork.ToShortDateString();
                    row["Начало работы по графику"] = item.GraphicStartWork;
                    row["Окончание работы по графику"] = item.GraphicEndWork;
                    row["Фактическое время начала работы"] = item.FactStartWork;
                    row["Фактическое время окончания работы"] = item.FactEndWork;
                    row["Уважительная причина отсутствия"] = item.IsReasondWorkCancelation ? "Да" : "-";
                    row["Прогул"] = item.NoWork ? "Да" : "-";
                    row["Комментарий"] = item.NoWorkComment;
                    row["Оплата без штрафов"] = item.MustBePay;
                    row["Фактическая оплата"] = item.FactPay;
                    row["Штрафы"] = item.Penalties;
                    row["Комментарий к оплате"] = item.PayComment;
                    dt.Rows.Add(row);
                }
                /*
                 * <div>Итого время работы по графику: <span>@Model.AmountGraphicWorkHours</span></div>
    <div>Итого фактическое время работы: <span>@Model.AmountFactWorkHours</span></div>
    <div>Итого оплата без штрафов: @Model.AmountMustBePay</div>
    <div>Итого к выплате: @Model.AmountFactPay</div>
                 * */
                DataTable d2 = new DataTable();
                d2.Columns.Add(new DataColumn("Итого время работы по графику", report.AmountGraphicWorkHours.GetType()));
                d2.Columns.Add(new DataColumn("Итого фактическое время работы", report.AmountFactWorkHours.GetType()));
                d2.Columns.Add(new DataColumn("Итого оплата без штрафов", report.AmountMustBePay.GetType()));
                d2.Columns.Add(new DataColumn("Итого к выплате", report.AmountFactPay.GetType()));
                DataRow row1 = d2.NewRow();
                row1["Итого время работы по графику"] = report.AmountGraphicWorkHours;
                row1["Итого фактическое время работы"] = report.AmountFactWorkHours;
                row1["Итого оплата без штрафов"] = report.AmountMustBePay;                
                row1["Итого к выплате"] = report.AmountFactPay;
                d2.Rows.Add(row1);

                dt.Merge(d2);

                return CreateExcelDocument(dt, xlsxFilePath, Response);
            }

            public static bool CreateExcelDocument<T>(List<T> list, string xlsxFilePath)
            {
                DataSet ds = new DataSet();
                ds.Tables.Add(ListToDataTable(list));

                return CreateExcelDocument(ds, xlsxFilePath);
            }
            #region HELPER_FUNCTIONS
            //  This function is adapated from: http://www.codeguru.com/forum/showthread.php?t=450171
            //  My thanks to Carl Quirion, for making it "nullable-friendly".
            public static DataTable ListToDataTable<T>(List<T> list)
            {
                DataTable dt = new DataTable();

                foreach (PropertyInfo info in typeof(T).GetProperties())
                {
                    dt.Columns.Add(new DataColumn(info.Name, GetNullableType(info.PropertyType)));
                }
                foreach (T t in list)
                {
                    DataRow row = dt.NewRow();
                    foreach (PropertyInfo info in typeof(T).GetProperties())
                    {
                        if (!IsNullableType(info.PropertyType))
                            row[info.Name] = info.GetValue(t, null);
                        else
                            row[info.Name] = (info.GetValue(t, null) ?? DBNull.Value);
                    }
                    dt.Rows.Add(row);
                }
                return dt;
            }
            private static Type GetNullableType(Type t)
            {
                Type returnType = t;
                if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                {
                    returnType = Nullable.GetUnderlyingType(t);
                }
                return returnType;
            }
            private static bool IsNullableType(Type type)
            {
                return (type == typeof(string) ||
                        type.IsArray ||
                        (type.IsGenericType &&
                         type.GetGenericTypeDefinition().Equals(typeof(Nullable<>))));
            }

            public static bool CreateExcelDocument(DataTable dt, string xlsxFilePath)
            {
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                bool result = CreateExcelDocument(ds, xlsxFilePath);
                ds.Tables.Remove(dt);
                return result;
            }
            #endregion

#if INCLUDE_WEB_FUNCTIONS
        /// <summary>
        /// Create an Excel file, and write it out to a MemoryStream (rather than directly to a file)
        /// </summary>
        /// <param name="dt">DataTable containing the data to be written to the Excel.</param>
        /// <param name="filename">The filename (without a path) to call the new Excel file.</param>
        /// <param name="Response">HttpResponse of the current page.</param>
        /// <returns>True if it was created succesfully, otherwise false.</returns>
        public static bool CreateExcelDocument(DataTable dt, string filename, System.Web.HttpResponseBase Response)
        {
            try
            {
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                CreateExcelDocumentAsStream(ds, filename, Response);
                ds.Tables.Remove(dt);
                return true;
            }
            catch (Exception ex)
            {
                Trace.WriteLine("Failed, exception thrown: " + ex.Message);
                return false;
            }
        }

        public static bool CreateExcelDocument<T>(List<T> list, string filename, System.Web.HttpResponseBase Response)
        {
            try
            {
                DataSet ds = new DataSet();
                ds.Tables.Add(ListToDataTable(list));
                CreateExcelDocumentAsStream(ds, filename, Response);
                return true;
            }
            catch (Exception ex)
            {
                Trace.WriteLine("Failed, exception thrown: " + ex.Message);
                return false;
            }
        }

        /// <summary>
        /// Create an Excel file, and write it out to a MemoryStream (rather than directly to a file)
        /// </summary>
        /// <param name="ds">DataSet containing the data to be written to the Excel.</param>
        /// <param name="filename">The filename (without a path) to call the new Excel file.</param>
        /// <param name="Response">HttpResponse of the current page.</param>
        /// <returns>Either a MemoryStream, or NULL if something goes wrong.</returns>
        public static bool CreateExcelDocumentAsStream(DataSet ds, string filename, System.Web.HttpResponseBase Response)
        {
            try
            {
                System.IO.MemoryStream stream = new System.IO.MemoryStream();
                using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))
                {
                    WriteExcelFile(ds, document);
                }
                stream.Flush();
                stream.Position = 0;

                Response.ClearContent();
                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";

                //  NOTE: If you get an "HttpCacheability does not exist" error on the following line, make sure you have
                //  manually added System.Web to this project's References.

                Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache);
                Response.AddHeader("content-disposition", "attachment; filename=" + filename);
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                byte[] data1 = new byte[stream.Length];
                stream.Read(data1, 0, data1.Length);
                stream.Close();
                Response.BinaryWrite(data1);
                Response.Flush();
                Response.End();

                return true;
            }
            catch (Exception ex)
            {
                Trace.WriteLine("Failed, exception thrown: " + ex.Message);
                return false;
            }
        }
#endif      //  End of "INCLUDE_WEB_FUNCTIONS" section

            /// <summary>
            /// Create an Excel file, and write it to a file.
            /// </summary>
            /// <param name="ds">DataSet containing the data to be written to the Excel.</param>
            /// <param name="excelFilename">Name of file to be written.</param>
            /// <returns>True if successful, false if something went wrong.</returns>
            public static bool CreateExcelDocument(DataSet ds, string excelFilename)
            {
                try
                {
                    using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook))
                    {
                        WriteExcelFile(ds, document);
                    }
                   // Trace.WriteLine("Successfully created: " + excelFilename);
                    return true;
                }
                catch (Exception ex)
                {
                    //Trace.WriteLine("Failed, exception thrown: " + ex.Message);
                    return false;
                }
            }

            private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
            {
                //  Create the Excel file contents.  This function is used when creating an Excel file either writing 
                //  to a file, or writing to a MemoryStream.
                spreadsheet.AddWorkbookPart();
                spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

                //  My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
                spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));

                //  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
                WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
                Stylesheet stylesheet = new Stylesheet();
                workbookStylesPart.Stylesheet = stylesheet;

                //  Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
                uint worksheetNumber = 1;
                foreach (DataTable dt in ds.Tables)
                {
                    //  For each worksheet you want to create
                    string workSheetID = "rId" + worksheetNumber.ToString();
                    string worksheetName = dt.TableName;

                    WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
                    newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();

                    // create sheet data
                    newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());

                    // save worksheet
                    WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
                    newWorksheetPart.Worksheet.Save();

                    // create the worksheet to workbook relation
                    if (worksheetNumber == 1)
                        spreadsheet.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());

                    spreadsheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                    {
                        Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
                        SheetId = (uint)worksheetNumber,
                        Name = dt.TableName
                    });

                    worksheetNumber++;
                }

                spreadsheet.WorkbookPart.Workbook.Save();
            }


            private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
            {
                var worksheet = worksheetPart.Worksheet;
                var sheetData = worksheet.GetFirstChild<SheetData>();

                string cellValue = "";

                //  Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
                //
                //  We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
                //  cells of data, we'll know if to write Text values or Numeric cell values.
                int numberOfColumns = dt.Columns.Count;
                bool[] IsNumericColumn = new bool[numberOfColumns];

                string[] excelColumnNames = new string[numberOfColumns];
                for (int n = 0; n < numberOfColumns; n++)
                    excelColumnNames[n] = GetExcelColumnName(n);

                //
                //  Create the Header row in our Excel Worksheet
                //
                uint rowIndex = 1;

                var headerRow = new Row { RowIndex = rowIndex };  // add a row at the top of spreadsheet
                sheetData.Append(headerRow);

                for (int colInx = 0; colInx < numberOfColumns; colInx++)
                {
                    DataColumn col = dt.Columns[colInx];
                    AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, headerRow);
                    IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32");
                }

                //
                //  Now, step through each row of data in our DataTable...
                //
                double cellNumericValue = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    // ...create a new row, and append a set of this row's data to it.
                    ++rowIndex;
                    var newExcelRow = new Row { RowIndex = rowIndex };  // add a row at the top of spreadsheet
                    sheetData.Append(newExcelRow);

                    for (int colInx = 0; colInx < numberOfColumns; colInx++)
                    {
                        cellValue = dr.ItemArray[colInx].ToString();

                        // Create cell with data
                        if (IsNumericColumn[colInx])
                        {
                            //  For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
                            //  If this numeric value is NULL, then don't write anything to the Excel file.
                            cellNumericValue = 0;
                            if (double.TryParse(cellValue, out cellNumericValue))
                            {
                                cellValue = cellNumericValue.ToString();
                                AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
                            }
                        }
                        else
                        {
                            //  For text cells, just write the input data straight out to the Excel file.
                            AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
                        }
                    }
                }
            }

            private static void AppendTextCell(string cellReference, string cellStringValue, Row excelRow)
            {
                //  Add a new Excel Cell to our Row 
                Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };
                CellValue cellValue = new CellValue();
                cellValue.Text = cellStringValue;
                cell.Append(cellValue);
                excelRow.Append(cell);
            }

            private static void AppendNumericCell(string cellReference, string cellStringValue, Row excelRow)
            {
                //  Add a new Excel Cell to our Row 
                Cell cell = new Cell() { CellReference = cellReference };
                CellValue cellValue = new CellValue();
                cellValue.Text = cellStringValue;
                cell.Append(cellValue);
                excelRow.Append(cell);
            }

            private static string GetExcelColumnName(int columnIndex)
            {
                //  Convert a zero-based column index into an Excel column reference  (A, B, C.. Y, Y, AA, AB, AC... AY, AZ, B1, B2..)
                //
                //  eg  GetExcelColumnName(0) should return "A"
                //      GetExcelColumnName(1) should return "B"
                //      GetExcelColumnName(25) should return "Z"
                //      GetExcelColumnName(26) should return "AA"
                //      GetExcelColumnName(27) should return "AB"
                //      ..etc..
                //
                if (columnIndex < 26)
                    return ((char)('A' + columnIndex)).ToString();

                char firstChar = (char)('A' + (columnIndex / 26) - 1);
                char secondChar = (char)('A' + (columnIndex % 26));

                return string.Format("{0}{1}", firstChar, secondChar);
            }
        }
}